package org.opendatakit.collect.android.provider;

import java.io.File;

import org.opendatakit.collect.android.application.Collect;
import org.opendatakit.collect.android.database.ODKSQLiteOpenHelper;
import org.opendatakit.collect.android.utilities.FileUtils;
import org.opendatakit.collect.android.utilities.FilterUtils;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class InstanceValueProvider {
	private static final String t = "InstanceValueProvider";

	public static class InstanceValueColumns {
		public static String DATABASE_NAME = "iv.db";
		public static int DATABASE_VERSION = 1;
		
		public static String INSTANCE_VALUES_TABLE = "attr_values";
		public static String SAVED_INSTANCE_VALUES_TABLE = "saved_attr_values";
		public static String INSTANCE_INFO_TABLE = "info_values";
		
		public static String _ID = "_id";
		public static String FORM_ID = "form_id";
		public static String VERSION = "version";
		public static String INSTANCE_ID = "instance_id";
		public static String NAME = "name";
		public static String VALUE = "value";
		
	}
	private IVDatabaseHelper mIvDb;
	
	public InstanceValueProvider() {
		mIvDb = new IVDatabaseHelper();
	}
	
	public static class IVDatabaseHelper extends ODKSQLiteOpenHelper {
		
		private static String dbPath() {
			String path = Collect.METADATA_PATH + File.separator + "jquery";
			FileUtils.createFolder(path);
			return path;
		}
	
	    public IVDatabaseHelper() {
	        super(dbPath(), 
	        	  InstanceValueColumns.DATABASE_NAME, null, InstanceValueColumns.DATABASE_VERSION);
	    }
	
	    @Override
	    public void onCreate(SQLiteDatabase db) {            
	       db.execSQL("CREATE TABLE " + InstanceValueColumns.INSTANCE_VALUES_TABLE + " (" 
	           + InstanceValueColumns._ID + " integer primary key, " 
	           + InstanceValueColumns.FORM_ID + " text not null, "
	           + InstanceValueColumns.VERSION + " text null, " 
	           + InstanceValueColumns.INSTANCE_ID + " text not null, "
	           + InstanceValueColumns.NAME + " text not null, "
	           + InstanceValueColumns.VALUE + " text null );");   
	
	       db.execSQL("CREATE TABLE " + InstanceValueColumns.SAVED_INSTANCE_VALUES_TABLE + " (" 
		           + InstanceValueColumns._ID + " integer primary key, " 
		           + InstanceValueColumns.FORM_ID + " text not null, "
		           + InstanceValueColumns.VERSION + " text null, " 
		           + InstanceValueColumns.INSTANCE_ID + " text not null, "
		           + InstanceValueColumns.NAME + " text not null, "
		           + InstanceValueColumns.VALUE + " text null );");   
		
	       db.execSQL("CREATE TABLE " + InstanceValueColumns.INSTANCE_INFO_TABLE + " (" 
	               + InstanceValueColumns._ID + " integer primary key, " 
	               + InstanceValueColumns.FORM_ID + " text not null, "
	               + InstanceValueColumns.VERSION + " text null, " 
	               + InstanceValueColumns.INSTANCE_ID + " text not null, "
	               + InstanceValueColumns.NAME + " text not null, "
	               + InstanceValueColumns.VALUE + " text null );");   
	    }
	
	
	    @Override
	    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	        Log.w(t, "Upgrading database from version " + oldVersion + " to " + newVersion
	                + ", which will destroy all old data");
	        db.execSQL("DROP TABLE IF EXISTS " + InstanceValueColumns.INSTANCE_VALUES_TABLE);
	        onCreate(db);
	    }
	}

	public synchronized void assertValue( String formId, String version, String instanceId, String path, String value ) {
		assertValue( InstanceValueColumns.INSTANCE_VALUES_TABLE, formId, version, instanceId, path, value );
	}

	public synchronized void assertSavedValue( String formId, String version, String instanceId, String path, String value ) {
		assertValue( InstanceValueColumns.INSTANCE_VALUES_TABLE, formId, version, instanceId, path, value );
		assertValue( InstanceValueColumns.SAVED_INSTANCE_VALUES_TABLE, formId, version, instanceId, path, value );
	}
	
	private synchronized void assertValue( String table, String formId, String version, String instanceId, String path, String value ) {
		Log.i(t, "assertValue(" + table + "," + formId + "," + version + "," + instanceId + "," + path + "): " + value);
		FilterUtils.FilterCriteria fc = FilterUtils.buildSelectionClause(
				new String[] {InstanceValueColumns.FORM_ID,
							  InstanceValueColumns.VERSION,
							  InstanceValueColumns.INSTANCE_ID,
							  InstanceValueColumns.NAME}, 
				new String[] {formId, version, instanceId, path}, null );

		SQLiteDatabase db = mIvDb.getWritableDatabase();
		boolean hasMultiple = false;
		Long id = null;
		db.beginTransaction();
		try {
			Cursor c = null;
			try {
	    		c = db.query( table, 
						   new String[] { InstanceValueColumns._ID, InstanceValueColumns.VALUE },
						   fc.selection, fc.selectionArgs, null, null, 
						   InstanceValueColumns._ID + " ASC");
	    		if ( c.getCount() != 0 ) {
	    			c.moveToFirst();
	    			id = c.getLong(c.getColumnIndex(InstanceValueColumns._ID));
	    			while ( !c.isLast() ) {
	    				c.moveToNext();
	    				hasMultiple = true;
	    				Log.w(t, "Additional records! " 
	    						+ Long.toString(c.getLong(c.getColumnIndex(InstanceValueColumns._ID))) 
	    						+ " value: " + c.getString(c.getColumnIndex(InstanceValueColumns.VALUE)));
	    			}
	    		}
			} finally {
				if ( c != null ) {
					c.close();
				}
			}
			
			ContentValues v = new ContentValues();
			v.put( InstanceValueColumns.FORM_ID, formId);
			v.put( InstanceValueColumns.VERSION, version);
			v.put( InstanceValueColumns.INSTANCE_ID, instanceId);
			v.put( InstanceValueColumns.NAME, path);
			v.put( InstanceValueColumns.VALUE, value);
			
			if ( id != null ) {
				if ( hasMultiple ) {
					// delete everything except the id entry...
					db.delete(table,
						fc.selection + " AND " +
						InstanceValueColumns._ID + " != " + Long.toString(id), fc.selectionArgs);
				}
				// update
				db.update(table, v, fc.selection, fc.selectionArgs);
			} else {
				// insert
				db.insertOrThrow(table,	null, v);
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
	public String getValue( String formId, String version, String instanceId, String path) {
		return getValue( InstanceValueColumns.INSTANCE_VALUES_TABLE, formId, version, instanceId, path);
	}
	
	public String getSavedValue( String formId, String version, String instanceId, String path) {
		return getValue( InstanceValueColumns.SAVED_INSTANCE_VALUES_TABLE, formId, version, instanceId, path);
	}

	public String getValue( String table, String formId, String version, String instanceId, String path) {
		FilterUtils.FilterCriteria fc = FilterUtils.buildSelectionClause(
				new String[] {InstanceValueColumns.FORM_ID,
							  InstanceValueColumns.VERSION,
							  InstanceValueColumns.INSTANCE_ID,
							  InstanceValueColumns.NAME}, 
				new String[] {formId, version, instanceId, path}, null );

		String value = null;
		Cursor c = null;
		try {
    		c = mIvDb.getReadableDatabase().query( table, 
					   new String[] { InstanceValueColumns._ID, InstanceValueColumns.VALUE },
					   fc.selection, fc.selectionArgs, null, null, 
					   InstanceValueColumns._ID + " ASC");
    		if ( c.getCount() != 0 ) {
    			c.moveToFirst();
    			value = c.getString(c.getColumnIndex(InstanceValueColumns.VALUE));
    			while ( !c.isLast() ) {
    				c.moveToNext();
    				Log.w(t, "Additional records! " 
    						+ Long.toString(c.getLong(c.getColumnIndex(InstanceValueColumns._ID))) 
    						+ " value: " + c.getString(c.getColumnIndex(InstanceValueColumns.VALUE)));
    			}
    		}
		} finally {
			if ( c != null ) {
				c.close();
			}
		}
		Log.i(t, "getValue(" + table + "," + formId + "," + version + "," + instanceId + "," + path + "): " + value);
		return value;
	}

	public void saveAllValues( String formId, String version, String instanceId ) {
		FilterUtils.FilterCriteria fc = FilterUtils.buildSelectionClause(
				new String[] {InstanceValueColumns.FORM_ID,
							  InstanceValueColumns.VERSION,
							  InstanceValueColumns.INSTANCE_ID,}, 
				new String[] {formId, version, instanceId}, null );
		
		SQLiteDatabase db = mIvDb.getWritableDatabase();
		db.beginTransaction();
		try {
			db.delete(InstanceValueColumns.SAVED_INSTANCE_VALUES_TABLE, fc.selection, fc.selectionArgs);
			String sql = "INSERT INTO " + InstanceValueColumns.SAVED_INSTANCE_VALUES_TABLE + " ("
			           + InstanceValueColumns.FORM_ID + ", "
			           + InstanceValueColumns.VERSION + ", " 
			           + InstanceValueColumns.INSTANCE_ID + ", "
			           + InstanceValueColumns.NAME + ", "
			           + InstanceValueColumns.VALUE + " ) SELECT "
			           + InstanceValueColumns.FORM_ID + ", "
			           + InstanceValueColumns.VERSION + ", " 
			           + InstanceValueColumns.INSTANCE_ID + ", "
			           + InstanceValueColumns.NAME + ", "
			           + InstanceValueColumns.VALUE + " FROM "
			           + InstanceValueColumns.INSTANCE_VALUES_TABLE + " WHERE " + fc.selection;
			db.execSQL( sql, fc.selectionArgs);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
	
	public boolean checkpointNotSaved( String formId, String version, String instanceId ) {
		FilterUtils.FilterCriteria fc = FilterUtils.buildSelectionClause(
				new String[] {InstanceValueColumns.FORM_ID,
							  InstanceValueColumns.VERSION,
							  InstanceValueColumns.INSTANCE_ID,}, 
				new String[] {formId, version, instanceId}, null );
		String[] joinedCriteria = new String[] {formId, version, instanceId, formId, version, instanceId};
		
		SQLiteDatabase db = mIvDb.getWritableDatabase();
		Cursor c = null;
		try {
			c = db.rawQuery("SELECT " 
		           + InstanceValueColumns.FORM_ID + ", "
		           + InstanceValueColumns.VERSION + ", " 
		           + InstanceValueColumns.INSTANCE_ID + ", "
		           + InstanceValueColumns.NAME + ", "
		           + InstanceValueColumns.VALUE + " FROM "
		           + InstanceValueColumns.SAVED_INSTANCE_VALUES_TABLE + " WHERE " + fc.selection
		           + " EXCEPT SELECT "
		           + InstanceValueColumns.FORM_ID + ", "
		           + InstanceValueColumns.VERSION + ", " 
		           + InstanceValueColumns.INSTANCE_ID + ", "
		           + InstanceValueColumns.NAME + ", "
		           + InstanceValueColumns.VALUE + " FROM "
		           + InstanceValueColumns.INSTANCE_VALUES_TABLE + " WHERE " + fc.selection,
		           joinedCriteria );
			
			return (c.getCount() != 0);
		} finally {
			if ( c != null ) {
				c.close();
			}
		}
	}
}
